import java.awt.List;
import java.io.ObjectInputStream.GetField;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;
import java.util.Vector;

import javax.swing.DefaultListModel;
import javax.swing.JComboBox;
import javax.swing.JLabel;
import javax.swing.JList;
import javax.swing.table.DefaultTableModel;
import javax.xml.crypto.Data;

import com.mysql.jdbc.ResultSetMetaData;


public class Connector {
	final private static String hostname = "db.uni-duisburg-essen.de";
	final private static String port = "3306";
	final private static String dbname = "sgthjunk_1";
	final private static String user = "sgthjunk";
	final private static String password = "nomtyirl";
	
	/*
	Verbindung wird aufgebaut
	*/
	static Connection conn;
	static int i = 0;
	static Statement stmt;

	public static boolean connection_check() {
		System.out.println("Anzahl Aufrufe " + i++);
		try {
			Class.forName("de.root1.jpmdbc.Driver");
		} catch (ClassNotFoundException e) {
		}
		if (conn != null) {
			System.out.println(conn == null);
			
			return true;
		} else
			return false;
	}

	public static void connection_start() {
		try {
			Properties connProperties = new Properties();
			connProperties.setProperty("user", user);
			connProperties.setProperty("password", password);
			connProperties.setProperty("host", hostname);
			connProperties.setProperty("port", port);
			String url = "jdbc:mysql://" + hostname + ":" + port + "/" + dbname;
			
			conn = (Connection) DriverManager.getConnection(url, user, password);
	
			
			stmt = (Statement) conn.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}
	
	public static boolean AuftraghinzuSQL (String Auftragsnummer){
		boolean check=false;
		
		try{
		ResultSet rs = null;
		String sqlCommand = "INSERT INTO Auftrag VALUES('"+ Auftragsnummer+"')";
		
		if (!connection_check()) {
			connection_start();
		}

			stmt.executeUpdate(sqlCommand);
		conn.close();
		conn = null;
		stmt.close();
		stmt = null;
	} catch (SQLException sqle) {
		System.out.println("SQLException: " + sqle.getMessage());
		System.out.println("SQLState: " + sqle.getSQLState());
		System.out.println("VendorError: " + sqle.getErrorCode());
		sqle.printStackTrace();
	}
		return check;
	}
	
	public static boolean StundenhinzuSQL (String AuftragCombo, String MitarbeiterCombo, String StundenTextfeld /*, String DatumTextFeld*/ , String JahrCombo, String MonatCombo, String TagCombo){
		boolean check=false;
		
		try{
		ResultSet rs = null;
		String sqlCommand = "INSERT INTO Auftrag_has_Mitarbeiter VALUES ('"+ AuftragCombo +"','"+ MitarbeiterCombo +"','"+ StundenTextfeld +"','"+ JahrCombo +"-"+ MonatCombo +"-"+ TagCombo +"')";
	System.out.println();
		if (!connection_check()) {
			connection_start();
		}
		stmt.executeUpdate(sqlCommand);	    
	    rs = null;
		conn.close();
		conn = null;
		stmt.close();
		stmt = null;
	} catch (SQLException sqle) {
		System.out.println("SQLException: " + sqle.getMessage());
		System.out.println("SQLState: " + sqle.getSQLState());
		System.out.println("VendorError: " + sqle.getErrorCode());
		sqle.printStackTrace();
	}
				
		return check;
		
	}
	
	public static boolean MitarbeiterhinzuSQL (String MitarbeiterVorname,String MitarbeiterNachname,String Stdverrechnungssatz){
		boolean check=false;
		
		try{
		ResultSet rs = null;
		String sqlCommand = "INSERT INTO Mitarbeiter VALUES (0,'"+ MitarbeiterVorname+"','"+MitarbeiterNachname+"','"+Stdverrechnungssatz+"')";
		
		if (!connection_check()) {
			connection_start();
		}
		stmt.executeUpdate(sqlCommand);	    
	    rs = null;
		conn.close();
		conn = null;
		stmt.close();
		stmt = null;
	} catch (SQLException sqle) {
		System.out.println("SQLException: " + sqle.getMessage());
		System.out.println("SQLState: " + sqle.getSQLState());
		System.out.println("VendorError: " + sqle.getErrorCode());
		sqle.printStackTrace();
	}
		
		return check;
		
		
	}
	
	
	
	public static JComboBox fuelleMitarbeiterSQL (JComboBox BlackBox){

		ResultSet rs = null;
		
		String sqlCommand = "SELECT Vorname,Nachname, idMitarbeiter FROM Mitarbeiter";
		if (!connection_check()) {
			connection_start();
		}
		try{
			rs = stmt.executeQuery(sqlCommand);
			if (rs.last() == true) {
				rs.beforeFirst();
				while (rs.next()) {

					BlackBox.addItem("("+rs.getString("idMitarbeiter")+") "+rs.getString("Vorname")+", "+rs.getString("Nachname"));
					
				}
			}
		conn.close();
		conn = null;
		stmt.close();
		stmt = null;
		}
		catch (SQLException sqle){
			System.out.println("SQLException: " + sqle.getMessage());
			System.out.println("SQLState: " + sqle.getSQLState());
			System.out.println("VendorError: " + sqle.getErrorCode());
			sqle.printStackTrace();
		}
		return BlackBox;
		
	}
	
	public static JComboBox fuelleAuftragsnummerSQL (JComboBox BlackBox){

		ResultSet rs = null;
		
		String sqlCommand = "SELECT Auftragsnummer FROM Auftrag";
		if (!connection_check()) {
			connection_start();
		}
		try{
			rs = stmt.executeQuery(sqlCommand);
			if (rs.last() == true) {
				rs.beforeFirst();
				while (rs.next()) {

					BlackBox.addItem(rs.getString("Auftragsnummer"));
				}
			}
		conn.close();
		conn = null;
		stmt.close();
		stmt = null;
		}
		catch (SQLException sqle){
			System.out.println("SQLException: " + sqle.getMessage());
			System.out.println("SQLState: " + sqle.getSQLState());
			System.out.println("VendorError: " + sqle.getErrorCode());
			sqle.printStackTrace();
		}
		return BlackBox;
		
	}
	
	
	
	
	public static JComboBox fuelleTag (JComboBox DatumTag)
	{
		String a ="";
		
		for (int i=1; i<=31;i++)
		{
			a= String.valueOf(i);
			DatumTag.addItem(""+a);
		}
		return DatumTag;
	}
	
	public static JComboBox fuelleMonat (JComboBox DatumMonat)
	{
	
		String a ="";
		
		for (int i=1; i<=12;i++)
		{
			a= String.valueOf(i);
			DatumMonat.addItem(""+a);
		}
			return DatumMonat;
	}
	
	public static JComboBox fuelleJahr (JComboBox DatumJahr)
	{
		String a ="";
		
		for (int i=2013; i<=2020;i++)
		{
			a= String.valueOf(i);
			DatumJahr.addItem(""+a);
		}
		return DatumJahr;
	}

	
	
	
	
	public static DefaultTableModel ausgabefensterSQL (DefaultTableModel WhiteBox) throws SQLException{

		ResultSet rs = null;
		Vector columnNames = new Vector();
		Vector rows = new Vector();
		String sqlCommand = "Select * From Auftrag_has_Mitarbeiter";
		if (!connection_check()) {
			connection_start();
		}
			connection_start();
			rs = stmt.executeQuery(sqlCommand);
			try {
	            ResultSetMetaData metaData = (ResultSetMetaData) rs.getMetaData();
	            int numberOfColumns = metaData.getColumnCount();
	            

	            // Get the column names
	            for (int column = 0; column < numberOfColumns; column++) {
	                columnNames.addElement(metaData.getColumnLabel(column + 1));
	                System.out.println(""+columnNames);
	            }

	            // Get all rows.
	            

	            while (rs.next()) {
	                Vector newRow = new Vector();

	                for (int i = 1; i <= numberOfColumns; i++) {
	                    newRow.addElement(rs.getObject(i));
	                    System.out.println(""+newRow);
	                }

	                rows.addElement(newRow);
	            }

	            
	        } catch (Exception e) {
	            e.printStackTrace();

	            return null;
	        }
	    
			conn.close();
			conn = null;
			stmt.close();
			stmt = null;
			DefaultTableModel Killer = new DefaultTableModel(rows,columnNames);
			return Killer;
	}
	
	
	
	public static void neueStundenSQL (String Mitarbeiter, String Auftrag, String Stunden){
		//UNIT-Test WB
		System.out.println(""+Mitarbeiter);
		System.out.println(""+Auftrag);
		System.out.println(""+Stunden);
		
	}
}
